package ha.web.server.database;

import ha.web.server.exceptions.LoginException;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



class DBConnector {

	private static DBConnector singleton = null;
	private static Connection con = null;
	private final static String dbUrl = "jdbc:mysql://localhost:3306/game_db";
	private final static String dbUser = "root";
	private final static String dbPassword = "1234";
	private Statement st = null;
	private ResultSet rs = null;

	protected static DBConnector getInstance() {
		if(singleton==null)
			singleton = new DBConnector();
		return singleton;
	}
	
	private DBConnector () {
		initDB();
		initTables();	
	}

	private void initTables() {
		try {

			con = getConnection();
			st = con.createStatement();
			DatabaseMetaData metadata = con.getMetaData();
			rs = metadata.getTables(null, null, "userlogin", null);
			if(! rs.next()) {
				String createTableString = "CREATE TABLE userlogin "+
						"(user_name VARCHAR(25) NOT NULL," +
						"nick_name VARCHAR(25)NOT NULL," +
						"password VARCHAR(25) NOT NULL,"+
						"admin VARCHAR(25) NOT NULL,"+
						"PRIMARY KEY (user_name,nick_name))";
				st.executeUpdate(createTableString);
			}

			rs = metadata.getTables(null, null, "statistics", null);
			if(! rs.next())
			{
				String createTableString = "CREATE TABLE statistics "+
						"(user_name VARCHAR(25) NOT NULL UNIQUE," +
						"games_won INT(50) NOT NULL,"+
						"games_lost INT(50) NOT NULL," +
						"games_resign INT(50) NOT NULL," +
						"bigest_jump INT(50) NOT NULL," +
						"longest INT(50) NOT NULL,"+
						"shortest INT(50) NOT NULL,"+
						"PRIMARY KEY ( user_name ))"; 
				st.executeUpdate(createTableString);
			}

		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
			System.exit(1);

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (con != null) {
					con.close();
				}

			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
				System.exit(1);
			}
		}
	}

	private void initDB() {

		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance(); 
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", dbUser,dbPassword);
			rs = con.getMetaData().getCatalogs();
			while (rs.next()) {
				String databaseName = rs.getString(1);
				if(databaseName.equals("game_db")){
					if (rs != null) {
						rs.close();
					}
					if (st != null) {
						st.close();
					}
					if (con != null) {
						con.close();
					}

					return;
				}
			}
			Statement st = con.createStatement();
			st.executeUpdate("CREATE DATABASE game_db");
		}
		catch(Exception e){
			e.printStackTrace();
			System.exit(1);
		} finally {

			try {
				if (rs != null)
					rs.close();
				if (st != null) 
					st.close();
				if (con != null) 
					con.close();

			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
				System.exit(1);
			}
		}

	}
	


	private  Connection getConnection() {
		try {
			con = DriverManager.getConnection(dbUrl, dbUser,dbPassword);
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		}
		return con;
	}

	protected void updateUserStatistics(UserId user, boolean won_game, boolean resigned,int jump,int move)
	{
		if (getStat(user) == null) {
			return;
		}
		String[] vals;
		vals = getStat(user).split(":");
		Integer won = Integer.parseInt(vals[1]);
		Integer lost= Integer.parseInt(vals[2]); 
		Integer resign = Integer.parseInt(vals[3]); 
		Integer bigestJump = Integer.parseInt(vals[4]); 
		Integer longest = Integer.parseInt(vals[5]); 
		Integer shortest = Integer.parseInt(vals[6]); 

		if (won_game)
			won++;
		else 
			lost++;
		if (resigned)
			resign++;
		if(move<shortest)
			shortest = move;
		if(move>shortest)
			shortest = move;

		String queryString = "UPDATE statistics SET games_won=?, games_lost=?,"+
				" games_resign=? ,bigest_jump=?,longest=?,shortest=? WHERE user_name = ?";
		try {
			PreparedStatement ptmt = null;
			con = getConnection();
			ptmt = con.prepareStatement(queryString);
			ptmt.setString(1, won.toString() );
			ptmt.setString(2, lost.toString());
			ptmt.setString(3, resign.toString());
			ptmt.setString(4, bigestJump.toString());
			ptmt.setString(5, longest.toString());
			ptmt.setString(6, shortest.toString());
			ptmt.setString(7, user.getName());
			ptmt.executeQuery();
			con.close();
		}
		catch (SQLException e) {

			return;
		}

	}

	protected String getStat(UserId user)
	{
		PreparedStatement ptmt = null;
		ResultSet resultSet = null;
		String queryString = "SELECT * FROM statistics WHERE user_name = ?";
		try {
			con = getConnection();
			ptmt = con.prepareStatement(queryString);
			ptmt.setString(1, user.getName());
			resultSet = ptmt.executeQuery();
			if (resultSet.next()){
				String rslt="";
				rslt = resultSet.getString("user_name") + ":" + 
						resultSet.getString("games_won")+ ":" + 
						resultSet.getString("games_lost")+ ":" + 
						resultSet.getString("games_resign")+ ":" +
						resultSet.getString("bigest_jump")+ ":" +
						resultSet.getString("longest")+ ":" +
						resultSet.getString("shortest");
				con.close();
				return rslt;
			}
		}
		catch (SQLException e) {
			return "error with db 1" ;
		}
		return "none";

	}

	protected String GetUserStatistics(UserId user)
	{
		String[] vals;
		if (getStat(user) == null) {
			return "";
		}
		vals = getStat(user).split(":");
		String rslt="";
		rslt = "Statistics for player " + vals[0]+"<br>";
		rslt += "Number of winning games: " + vals[1]+"<br>";
		rslt += "Number of losing games: " + vals[2]+"<br>";
		rslt += "Number of resigned games: " + vals[3]+"<br>";
		rslt += "The bigest jump during game: " + vals[4]+"<br>";
		rslt += "The longest game (no. of moves): " + vals[4]+"<br>";
		rslt += "The shortest game (no. of moves): " + vals[5]+"<br>";
		return rslt;
	}

	protected boolean addUserAtDB(String name,String nickName,String pass,Boolean admin) throws LoginException {
		PreparedStatement ptmt = null;
		ResultSet resultSet = null;
		String queryString = 
				"SELECT user_name FROM userlogin WHERE user_name = ?";
		try {
			con = getConnection();
			ptmt = con.prepareStatement(queryString);
			ptmt.setString(1, name);
			resultSet = ptmt.executeQuery();
			if (resultSet.next() && 
					(resultSet.getString("user_name").equals(name) ||
							resultSet.getString("nick_name").equals(nickName))) {	
				con.close();
				throw new LoginException("DB Error: user allrady exist");
			}
			queryString = "INSERT INTO userlogin (user_name,nick_name,password, admin) VALUES (?,?,?,?)";
			ptmt = con.prepareStatement(queryString);
			ptmt.setString(1,name);
			ptmt.setString(2, nickName);
			ptmt.setString(3, pass);
			ptmt.setString(4, admin.toString());
			ptmt.execute();
			queryString = 
					"INSERT INTO statistics (user_name, games_won, games_lost, games_resign,"+
							"bigest_jump,longest,shortest) VALUES (?,0,0,0,0,0,0)";
			ptmt = con.prepareStatement(queryString);
			ptmt.setString(1, name);
			ptmt.execute();

			con.close();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}


	protected UserId validateLogin(String name, String pass) {
		PreparedStatement ptmt = null;
		ResultSet resultSet = null;
		String nickName = null;
		boolean admin = false;
		String queryString = 
				"SELECT * FROM userlogin WHERE user_name = ?";
		try {
//			Connection con = getInstance().getConnection();
			con = getConnection();
			ptmt = con.prepareStatement(queryString);
			ptmt.setString(1, name);
			resultSet = ptmt.executeQuery();
			if (resultSet.next() && 
					resultSet.getString("password").equals(pass)) {
				nickName = resultSet.getString("nick_name");
				admin = Boolean.valueOf(resultSet.getString("admin"));
				con.close();
			}
		} catch (Exception e) {
			return null;
		}
		return new UserId(name,nickName,pass,admin);
	}

}
